﻿using System;
using System.Data.Odbc;
using System.Linq;
using System.Text;

namespace Centido.Core
{
    /// <summary>
    ///   A set of table insert operations.
    /// </summary>
    public static class TableInsertOperations
    {
        /// <summary>
        ///   Inserts a new row with the specified id.
        /// </summary>
        /// <param name="table">The target table.</param>
        /// <param name = "rowId">The id of the new row.</param>
        /// <param name="dataSource">The target data source.</param>
        /// <param name = "values">The list of column values.</param>
        private static void InsertRowImplementation(Table table, Guid rowId,
            DataSource dataSource, params ColumnValue[] values)
        {
            // List of comma separated column names
            var columnList = new StringBuilder(Db.Tables.UserTable.Col.Id);

            if (values != null)
                values.ToList().ForEach(value => columnList.Append(",").Append(value.ColumnName));

            var tableName = table.GetDataSourceName(dataSource);

            // List of comma separated parameters
            var valueList = new StringBuilder("?");

            if (values != null)
                values.ToList().ForEach(value => valueList.Append(",?"));

            var rowCount = table.Domain.DbAccess.ExecuteNonQuery(
                cmd =>
                {
                    cmd.CommandText = string.Format(
                        "insert into {0} ({1}) values({2})", tableName, columnList, valueList);

                    // Adding parameters values
                    cmd.Parameters.Add(
                        table.Domain.DbAccess.CreateParameter(Db.Tables.UserTable.Col.Id,
                        OdbcType.Binary, rowId.ToByteArray()));

                    if (values != null)
                    {
                        values.ToList().ForEach(
                            value =>
                            {
                                var parameter = table.Domain.DbAccess.CreateParameter(
                                    value.ColumnName, value.Column.ValueOdbcType, DbValue.GetDbValue(value.Value));

                                cmd.Parameters.Add(parameter);
                            });
                    }
                });

            if (rowCount < 1)
                throw new DbOperationException();
        }


        /// <summary>
        ///   Inserts a new row with the specified values of the table columns. 
        /// </summary>
        /// <remarks>
        /// <para>A row will be inserted into the user view of the internal table.</para>
        /// <para>The current user has to have the insert permission on this table in order to execute this function.</para>
        /// </remarks>
        /// <param name="table">The target table.</param>
        /// <param name = "values">The table column values.</param>
        /// <exception cref="System.ArgumentNullException">Thrown when one of the specified values of the table columns is null.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown the current user has no permission to insert into the table.</exception>
        /// <returns>Returns the id of the inserted row.</returns>
        /// <example>
        ///   The following example shows how to insert a row with the specified values of the table columns.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///             
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a new table
        ///             var table = domain.AddTable("students");
        /// 
        ///             //Creating table columns
        ///             var surnameColumn = table.AddColumn("surname", ColumnType.String);
        ///             var birthdayColumn = table.AddColumn("birthday", ColumnType.DateTime);
        /// 
        ///             //Granting insert and select permissions
        ///             table.Grant(DataAction.Insert);
        ///             table.Grant(DataAction.Select);
        ///             
        ///             //Inserting two students
        ///             table.InsertRow(surnameColumn.Value("Smith"),
        ///                 birthdayColumn.Value(DateTime.Now.AddYears(-25)));
        ///             table.InsertRow(surnameColumn.Value("Brown"),
        ///                 birthdayColumn.Value(DateTime.Now.AddYears(-18)));
        /// 
        ///             var surnameColumnName = surnameColumn.Name;
        ///             var birthdayColumnName = birthdayColumn.Name;
        /// 
        ///             //Reading the inserted students
        ///             domain.DbAccess.ExecuteReader(cmd =>
        ///             {
        ///                 cmd.CommandText = string.Format("select id, {0}, {1} from {2}",
        ///                     surnameColumn.Name, birthdayColumn.Name, table.FullName);
        ///             }, reader =>
        ///             {
        ///                 var id = DbValue.GetGuid(reader["id"]);
        ///                 var surname = DbValue.GetString(reader[surnameColumnName]);
        ///                 var birthday = DbValue.GetDateTime(reader[birthdayColumnName]);
        /// 
        ///                 Console.WriteLine("Student id = {0}, surname = {1}, birthday = {2}",
        ///                     id, surname, birthday);
        ///             });
        ///         }
        ///     }
        /// </code>
        /// </example>
        public static Guid InsertRow(this Table table, params ColumnValue[] values)
        {
            if (table == null)
                throw new ArgumentNullException("table");

            var rowId = Guid.NewGuid();

            InsertRowImplementation(table, rowId, DataSource.UserView, values);

            return rowId;
        }


        /// <summary>
        ///   Inserts a new row with the specified id and values of the table columns. 
        /// </summary>
        /// <remarks>
        /// <para>A row will be inserted into the user view of the internal table.</para>
        /// <para>The values of such system columns as Owner, InsertedOn, InsertedBy, UpdatedOn, UpdatedBy and RowVersion are
        /// set after executing this function.</para>
        /// <para>The current user has to have the insert permission on this table in order to execute this function.</para>
        /// </remarks>
        /// <param name="table">The target table.</param>
        /// <param name="rowId">The id of the new row.</param>
        /// <param name = "values">The table column values.</param>
        /// <exception cref="System.ArgumentNullException">Thrown when one of the specified values of the table columns is null.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown the current user has no permission to insert into the table.</exception>
        /// <returns>Returns the id of the inserted row.</returns>
        /// <example>
        ///   The following example shows how to insert a row with the specified values of the table columns.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///             
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a new table
        ///             var table = domain.AddTable("students");
        /// 
        ///             //Creating table columns
        ///             var surnameColumn = table.AddColumn("surname", ColumnType.String);
        ///             var birthdayColumn = table.AddColumn("birthday", ColumnType.DateTime);
        /// 
        ///             //Granting insert and select permissions
        ///             table.Grant(DataAction.Insert);
        ///             table.Grant(DataAction.Select);
        ///             
        ///             //Inserting two students
        ///             table.InsertRow(Guid.NewGuid(), surnameColumn.Value("Smith"),
        ///                 birthdayColumn.Value(DateTime.Now.AddYears(-25)));
        ///             table.InsertRow(Guid.NewGuid(), surnameColumn.Value("Brown"),
        ///                 birthdayColumn.Value(DateTime.Now.AddYears(-18)));
        /// 
        ///             var surnameColumnName = surnameColumn.Name;
        ///             var birthdayColumnName = birthdayColumn.Name;
        /// 
        ///             //Reading the inserted students
        ///             domain.DbAccess.ExecuteReader(cmd =>
        ///             {
        ///                 cmd.CommandText = string.Format("select id, {0}, {1} from {2}",
        ///                     surnameColumn.Name, birthdayColumn.Name, table.FullName);
        ///             }, reader =>
        ///             {
        ///                 var id = DbValue.GetGuid(reader["id"]);
        ///                 var surname = DbValue.GetString(reader[surnameColumnName]);
        ///                 var birthday = DbValue.GetDateTime(reader[birthdayColumnName]);
        /// 
        ///                 Console.WriteLine("Student id = {0}, surname = {1}, birthday = {2}",
        ///                     id, surname, birthday);
        ///             });
        ///         }
        ///     }
        /// </code>
        /// </example>
        public static void InsertRow(this Table table, Guid rowId, params ColumnValue[] values)
        {
            if (table == null)
                throw new ArgumentNullException("table");

            InsertRowImplementation(table, rowId, DataSource.UserView, values);
        }


        /// <summary>
        ///   Inserts a new row with the specified values of the table columns.
        /// </summary>
        /// <remarks>
        /// <para>The current user has to have the insert permission on the target data source
        ///  in order to execute this function.</para>
        /// </remarks>
        /// <param name="table">The target table.</param>
        /// <param name="dataSource">The target data source.</param>
        /// <param name="values">The table column values.</param>
        /// <returns>Returns the id of the inserted row.</returns>
        /// <exception cref="System.ArgumentNullException">Thrown when one of the specified values of the table columns is null.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown the current user has no permission to insert into the table.</exception>
        /// <example>
        ///   The following example shows how to insert a row with the specified values of the table columns.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///             
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a new table
        ///             var table = domain.AddTable("students");
        /// 
        ///             //Creating table columns
        ///             var surnameColumn = table.AddColumn("surname", ColumnType.String);
        ///             var birthdayColumn = table.AddColumn("birthday", ColumnType.DateTime);
        /// 
        ///             //Granting insert and select permissions
        ///             table.Grant(DataAction.Insert);
        ///             table.Grant(DataAction.Select);
        ///             
        ///             //Inserting two students
        ///             table.InsertRow(DataSource.UserView, surnameColumn.Value("Smith"),
        ///                 birthdayColumn.Value(DateTime.Now.AddYears(-25)));
        ///             table.InsertRow(DataSource.UserView, surnameColumn.Value("Brown"),
        ///                 birthdayColumn.Value(DateTime.Now.AddYears(-18)));
        /// 
        ///             var surnameColumnName = surnameColumn.Name;
        ///             var birthdayColumnName = birthdayColumn.Name;
        /// 
        ///             //Reading the inserted students
        ///             domain.DbAccess.ExecuteReader(cmd =>
        ///             {
        ///                 cmd.CommandText = string.Format("select id, {0}, {1} from {2}",
        ///                     surnameColumn.Name, birthdayColumn.Name, table.FullName);
        ///             }, reader =>
        ///             {
        ///                 var id = DbValue.GetGuid(reader["id"]);
        ///                 var surname = DbValue.GetString(reader[surnameColumnName]);
        ///                 var birthday = DbValue.GetDateTime(reader[birthdayColumnName]);
        /// 
        ///                 Console.WriteLine("Student id = {0}, surname = {1}, birthday = {2}",
        ///                     id, surname, birthday);
        ///             });
        ///         }
        ///     }
        /// </code>
        /// </example>
        public static Guid InsertRow(this Table table, DataSource dataSource,
            params ColumnValue[] values)
        {
            if (table == null)
                throw new ArgumentNullException("table");

            var rowId = Guid.NewGuid();

            InsertRowImplementation(table, rowId, dataSource, values);

            return rowId;
        }


        /// <summary>
        ///   Inserts a new row with the specified id and values of the table columns. 
        /// </summary>
        /// <remarks>
        /// <para>The current user has to have the insert permission on the specified data source
        ///  in order to execute this function.</para>
        /// </remarks>
        /// <param name="table">The target table.</param>
        /// <param name="rowId">The id of the new row.</param>
        /// <param name="dataSource">The target data source.</param>
        /// <param name="values">The table column values.</param>
        /// <exception cref="System.ArgumentNullException">Thrown when one of the specified values of the table columns is null.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown the current user has no permission
        ///  to insert into the specified data source.</exception>
        /// <example>
        ///   The following example shows how to insert a row with the specified values of the table columns.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///             
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a new table
        ///             var table = domain.AddTable("students");
        /// 
        ///             //Creating table columns
        ///             var surnameColumn = table.AddColumn("surname", ColumnType.String);
        ///             var birthdayColumn = table.AddColumn("birthday", ColumnType.DateTime);
        /// 
        ///             //Granting insert and select permissions
        ///             table.Grant(DataAction.Insert);
        ///             table.Grant(DataAction.Select);
        ///             
        ///             //Inserting two students
        ///             table.InsertRow(Guid.NewGuid(), DataSource.UserView, surnameColumn.Value("Smith"),
        ///                 birthdayColumn.Value(DateTime.Now.AddYears(-25)));
        ///             table.InsertRow(Guid.NewGuid(), DataSource.UserView, surnameColumn.Value("Brown"),
        ///                 birthdayColumn.Value(DateTime.Now.AddYears(-18)));
        /// 
        ///             var surnameColumnName = surnameColumn.Name;
        ///             var birthdayColumnName = birthdayColumn.Name;
        /// 
        ///             //Reading the inserted students
        ///             domain.DbAccess.ExecuteReader(cmd =>
        ///             {
        ///                 cmd.CommandText = string.Format("select id, {0}, {1} from {2}",
        ///                     surnameColumn.Name, birthdayColumn.Name, table.FullName);
        ///             }, reader =>
        ///             {
        ///                 var id = DbValue.GetGuid(reader["id"]);
        ///                 var surname = DbValue.GetString(reader[surnameColumnName]);
        ///                 var birthday = DbValue.GetDateTime(reader[birthdayColumnName]);
        /// 
        ///                 Console.WriteLine("Student id = {0}, surname = {1}, birthday = {2}",
        ///                     id, surname, birthday);
        ///             });
        ///         }
        ///     }
        /// </code>
        /// </example>
        public static void InsertRow(this Table table, Guid rowId, DataSource dataSource,
            params ColumnValue[] values)
        {
            if (table == null)
                throw new ArgumentNullException("table");

            InsertRowImplementation(table, rowId, dataSource, values);
        }
    }
}
